﻿Imports System.Data.SqlClient

Public Class ClientesDAL
    Dim cmd As SqlCommand
    Dim objD As Dados
    Dim sql As String = ""

    Sub New()

    End Sub

    'INSERIR PESSOA 
    Function inserir(ByVal PES_NOME As String, ByVal PES_LOGRADOURO As String, ByVal PES_COMPLEMENTO As String, ByVal PES_NUMERO As String,
                     ByVal PES_BAIRRO As String, ByVal COD_CIDADE As Integer, ByVal PES_CEP As String, ByVal PES_LOGRADOURO_COB As String, ByVal PES_COMPLEMENTO_COB As String, ByVal PES_NUMERO_COB As String,
                     ByVal PES_BAIRRO_COB As String, ByVal PES_CEP_COB As String, ByVal PES_TIPO_FJ As Char,
                     ByVal PES_TELEFONE_FIXO As String, ByVal PES_CELULAR As String, ByVal PES_TELEFONE_RECADO As String,
                     ByVal PF_DATA_NASCIMENTO As Date, ByVal PF_RG As String, ByVal PF_CPF As String,
                     ByVal PJ_NOME_FANTASIA As String, ByVal PJ_RAMO As String, ByVal PJ_CNPJ As String, ByVal PJ_IE As String, ByVal status As Char)
        cmd = New SqlCommand
        objD = New Dados
        Dim PES_CODIGO As Integer
        Try

            sql = "INSERT INTO PESSOA (PES_NOME, PES_LOGRADOURO, PES_COMPLEMENTO, PES_NUMERO, PES_BAIRRO, COD_CIDADE, PES_CEP, " & _
            "PES_LOGRADOURO_COB, PES_COMPLEMENTO_COB, PES_NUMERO_COB, PES_BAIRRO_COB, PES_CEP_COB, " & _
            "PES_TELEFONE_FIXO, PES_CELULAR, PES_TELEFONE_RECADO, PES_TIPO_FJ)" & _
            "VALUES (@PES_NOME, @PES_LOGRADOURO, @PES_COMPLEMENTO, @PES_NUMERO, @PES_BAIRRO, @COD_CIDADE, @PES_CEP, " & _
            "@PES_LOGRADOURO_COB, @PES_COMPLEMENTO_COB, @PES_NUMERO_COB, @PES_BAIRRO_COB, @PES_CEP_COB, " & _
            "@PES_TELEFONE_FIXO, @PES_CELULAR, @PES_TELEFONE_RECADO, @PES_TIPO_FJ)"

            cmd.CommandText = sql
            cmd.Parameters.Add(New SqlParameter("@PES_NOME", SqlDbType.VarChar)).Value = PES_NOME
            cmd.Parameters.Add(New SqlParameter("@PES_LOGRADOURO", SqlDbType.VarChar)).Value = PES_LOGRADOURO
            If PES_COMPLEMENTO <> "" Then
                cmd.Parameters.Add(New SqlParameter("@PES_COMPLEMENTO", SqlDbType.VarChar)).Value = PES_COMPLEMENTO
            Else
                cmd.Parameters.Add(New SqlParameter("@PES_COMPLEMENTO", SqlDbType.VarChar)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@PES_NUMERO", SqlDbType.VarChar)).Value = PES_NUMERO
            cmd.Parameters.Add(New SqlParameter("@PES_BAIRRO", SqlDbType.VarChar)).Value = PES_BAIRRO
            cmd.Parameters.Add(New SqlParameter("@COD_CIDADE", SqlDbType.Int)).Value = COD_CIDADE
            cmd.Parameters.Add(New SqlParameter("@PES_CEP", SqlDbType.VarChar, 9)).Value = PES_CEP
            cmd.Parameters.Add(New SqlParameter("@PES_LOGRADOURO_COB", SqlDbType.VarChar)).Value = PES_LOGRADOURO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_COMPLEMENTO_COB", SqlDbType.VarChar)).Value = PES_COMPLEMENTO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_NUMERO_COB", SqlDbType.VarChar)).Value = PES_NUMERO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_BAIRRO_COB", SqlDbType.VarChar)).Value = PES_BAIRRO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_CEP_COB", SqlDbType.VarChar, 9)).Value = PES_CEP_COB
            cmd.Parameters.Add(New SqlParameter("@PES_TELEFONE_FIXO", SqlDbType.VarChar)).Value = PES_TELEFONE_FIXO
            cmd.Parameters.Add(New SqlParameter("@PES_CELULAR", SqlDbType.VarChar)).Value = PES_CELULAR
            cmd.Parameters.Add(New SqlParameter("@PES_TELEFONE_RECADO", SqlDbType.VarChar)).Value = PES_TELEFONE_RECADO
            cmd.Parameters.Add(New SqlParameter("@PES_TIPO_FJ", SqlDbType.Char)).Value = PES_TIPO_FJ
            PES_CODIGO = objD.executacomandoAI(cmd, "PESSOA")

            'Cadastrar tipopessoa
            If (PES_TIPO_FJ) = "F" Then
                inserir(PES_CODIGO, PF_DATA_NASCIMENTO, PF_RG, PF_CPF, status)
            Else
                inserir(PES_CODIGO, PJ_NOME_FANTASIA, PJ_RAMO, PJ_CNPJ, PJ_IE, status)
            End If

            'Cadastrar cliente
            inserir(PES_CODIGO)


        Catch ex As Exception
            Throw ex
        End Try
        Return PES_CODIGO
    End Function

    Sub alterar(ByVal PES_CODIGO As Integer, ByVal PES_NOME As String, ByVal PES_LOGRADOURO As String, ByVal PES_COMPLEMENTO As String, ByVal PES_NUMERO As String,
                     ByVal PES_BAIRRO As String, ByVal COD_CIDADE As Integer, ByVal PES_CEP As String, ByVal PES_LOGRADOURO_COB As String, ByVal PES_COMPLEMENTO_COB As String, ByVal PES_NUMERO_COB As String,
                     ByVal PES_BAIRRO_COB As String, ByVal PES_CEP_COB As String, ByVal PES_TIPO_FJ As Char,
                     ByVal PES_TELEFONE_FIXO As String, ByVal PES_CELULAR As String, ByVal PES_TELEFONE_RECADO As String,
                     ByVal PF_DATA_NASCIMENTO As Date, ByVal PF_RG As String, ByVal PF_CPF As String,
                     ByVal PJ_NOME_FANTASIA As String, ByVal PJ_RAMO As String, ByVal PJ_CNPJ As String, ByVal PJ_IE As String)
        cmd = New SqlCommand
        objD = New Dados
        Try
            sql = "UPDATE PESSOA SET PES_NOME=@PES_NOME, PES_LOGRADOURO=@PES_LOGRADOURO, PES_COMPLEMENTO=@PES_COMPLEMENTO, " & _
                  "PES_NUMERO=@PES_NUMERO, PES_BAIRRO=@PES_BAIRRO, COD_CIDADE=@COD_CIDADE, PES_CEP=@PES_CEP, " & _
                  "PES_LOGRADOURO_COB=@PES_LOGRADOURO_COB, PES_COMPLEMENTO_COB=@PES_COMPLEMENTO_COB, PES_NUMERO_COB=@PES_NUMERO_COB, " & _
                  "PES_BAIRRO_COB=@PES_BAIRRO_COB, PES_CEP_COB=@PES_CEP_COB, " & _
                  "PES_TELEFONE_FIXO=@PES_TELEFONE_FIXO, PES_CELULAR=@PES_CELULAR, PES_TELEFONE_RECADO=@PES_TELEFONE_RECADO, " & _
                  "PES_TIPO_FJ=@PES_TIPO_FJ" & _
                  "WHERE PES_CODIGO=@PES_CODIGO"
            cmd.CommandText = sql
            cmd.Parameters.Add(New SqlParameter("@pes_codigo", SqlDbType.Int)).Value = PES_CODIGO
            cmd.Parameters.Add(New SqlParameter("@PES_NOME", SqlDbType.VarChar)).Value = PES_NOME
            cmd.Parameters.Add(New SqlParameter("@PES_LOGRADOURO", SqlDbType.VarChar)).Value = PES_LOGRADOURO
            If PES_COMPLEMENTO <> "" Then
                cmd.Parameters.Add(New SqlParameter("@PES_COMPLEMENTO", SqlDbType.VarChar)).Value = PES_COMPLEMENTO
            Else
                cmd.Parameters.Add(New SqlParameter("@PES_COMPLEMENTO", SqlDbType.VarChar)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@PES_NUMERO", SqlDbType.VarChar)).Value = PES_NUMERO
            cmd.Parameters.Add(New SqlParameter("@PES_BAIRRO", SqlDbType.VarChar)).Value = PES_BAIRRO
            cmd.Parameters.Add(New SqlParameter("@COD_CIDADE", SqlDbType.Int)).Value = COD_CIDADE
            cmd.Parameters.Add(New SqlParameter("@PES_CEP", SqlDbType.VarChar, 9)).Value = PES_CEP
            cmd.Parameters.Add(New SqlParameter("@PES_LOGRADOURO_COB", SqlDbType.VarChar)).Value = PES_LOGRADOURO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_COMPLEMENTO_COB", SqlDbType.VarChar)).Value = PES_COMPLEMENTO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_NUMERO_COB", SqlDbType.VarChar)).Value = PES_NUMERO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_BAIRRO_COB", SqlDbType.VarChar)).Value = PES_BAIRRO_COB
            cmd.Parameters.Add(New SqlParameter("@PES_CEP_COB", SqlDbType.VarChar, 9)).Value = PES_CEP_COB
            cmd.Parameters.Add(New SqlParameter("@PES_TELEFONE_FIXO", SqlDbType.VarChar)).Value = PES_TELEFONE_FIXO
            cmd.Parameters.Add(New SqlParameter("@PES_CELULAR", SqlDbType.VarChar)).Value = PES_CELULAR
            cmd.Parameters.Add(New SqlParameter("@PES_TELEFONE_RECADO", SqlDbType.VarChar)).Value = PES_TELEFONE_RECADO
            cmd.Parameters.Add(New SqlParameter("@PES_TIPO_FJ", SqlDbType.Char)).Value = PES_TIPO_FJ
            objD.executacomando(cmd)

            'alterar tipopessoa
            If (PES_TIPO_FJ) = "F" Then
                alterar(PES_CODIGO, PF_DATA_NASCIMENTO, PF_RG, PF_CPF)
            Else
                alterar(PES_CODIGO, PJ_NOME_FANTASIA, PJ_RAMO, PJ_CNPJ, PJ_IE)
            End If

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing

    End Sub


    ''INSERIR PESSOA FISICA
    Private Function inserir(ByVal PES_CODIGO As Integer,
                     ByVal PF_DATA_NASCIMENTO As Date, ByVal PF_RG As String, ByVal PF_CPF As String, ByVal PF_ATIVO_SN As Char)
        cmd = New SqlCommand
        objD = New Dados
        Dim CodCliente As Integer
        Try

            sql = "INSERT INTO PESSOA_FISICA (PES_CODIGO, PF_DATA_NASCIMENTO, PF_RG, PF_CPF, PF_ATIVO_SN) VALUES " & _
                     "(@PES_CODIGO, @PF_DATA_NASCIMENTO, @PF_RG, @PF_CPF, @PF_ATIVO_SN)"
            cmd.Parameters.Add(New SqlParameter("@PES_CODIGO", SqlDbType.Int)).Value = PES_CODIGO
            cmd.Parameters.Add(New SqlParameter("@PF_DATA_NASCIMENTO", SqlDbType.Date)).Value = PF_DATA_NASCIMENTO
            cmd.Parameters.Add(New SqlParameter("@PF_RG", SqlDbType.VarChar, 15)).Value = PF_RG
            cmd.Parameters.Add(New SqlParameter("@PF_CPF", SqlDbType.Char, 14)).Value = PF_CPF
            cmd.Parameters.Add(New SqlParameter("@PF_ATIVO_SN", SqlDbType.Char, 1)).Value = PF_ATIVO_SN
            cmd.CommandText = sql
            CodCliente = objD.executacomandoAI(cmd, "PESSOA_FISICA")
            objD = Nothing

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing
        Return CodCliente
    End Function


    ''INSERIR PESSOA JURIDICA
    Private Function inserir(ByVal PES_CODIGO As Integer,
                     ByVal PJ_NOME_FANTASIA As String, ByVal PJ_RAMO As String, ByVal PJ_CNPJ As String, ByVal PJ_IE As String, ByVal PJ_ATIVO_SN As Char)
        cmd = New SqlCommand
        objD = New Dados
        Dim CodCliente As Integer

        Try
            sql = "INSERT INTO PESSOA_JURIDICA (PES_CODIGO, PJ_NOME_FANTASIA, PJ_RAMO, PJ_CNPJ, PJ_IE) " & _
                    "VALUES (@PES_CODIGO, @PJ_NOME_FANTASIA, @PJ_RAMO, @PJ_CNPJ, @PJ_IE)"
            cmd.Parameters.Add(New SqlParameter("@PES_CODIGO", SqlDbType.Int)).Value = PES_CODIGO
            cmd.Parameters.Add(New SqlParameter("@PJ_NOME_FANTASIA", SqlDbType.VarChar, 150)).Value = PJ_NOME_FANTASIA
            cmd.Parameters.Add(New SqlParameter("@PJ_RAMO", SqlDbType.VarChar, 50)).Value = PJ_RAMO
            cmd.Parameters.Add(New SqlParameter("@PJ_CNPJ", SqlDbType.VarChar, 19)).Value = PJ_CNPJ
            cmd.Parameters.Add(New SqlParameter("@PJ_IE", SqlDbType.VarChar, 25)).Value = PJ_IE
            cmd.Parameters.Add(New SqlParameter("@PJ_ATIVO_SN", SqlDbType.Char, 1)).Value = PJ_ATIVO_SN
            cmd.CommandText = sql
            CodCliente = objD.executacomandoAI(cmd, "PESSOA_JURIDICA")

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing
        Return CodCliente
    End Function

    ''INSERIR CLIENTE 
    Private Sub inserir(ByVal PES_CODIGO As Integer)
        cmd = New SqlCommand
        objD = New Dados
        Try
            sql = "INSERT INTO CLIENTES (PES_CODIGO) VALUES (@PES_CODIGO)"
            cmd.Parameters.Add(New SqlParameter("@PES_CODIGO", SqlDbType.Int)).Value = PES_CODIGO
            cmd.CommandText = sql
            objD.executacomando(cmd)
            objD = Nothing
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing
    End Sub

    'Inserir email
    Public Sub inseriremail(ByVal descricao As String, pessoa As Integer)
        cmd = New SqlCommand
        objD = New Dados
        Try
            sql = "INSERT INTO email (email_descricao, pes_codigo) VALUES (@descrica, @codigo)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = pessoa
            cmd.CommandText = sql
            objD.executacomando(cmd)
            objD = Nothing
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing
    End Sub

    Sub excluiremail(email As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "delete from email where email_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = email
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function localizaremail(email As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select * from email where email_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = email
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    ''ALTERAR PESSOA FISICA
    Private Sub alterar(ByVal PES_CODIGO As Integer,
                     ByVal PF_DATA_NASCIMENTO As Date, ByVal PF_RG As String, ByVal PF_CPF As String)
        cmd = New SqlCommand
        objD = New Dados
        Try
            sql = "UPDATE PESSOA_FISICA SET PF_DATA_NASCIMENTO=@PF_DATA_NASCIMENTO, PF_RG=@PF_RG, PF_CPF=@PF_CPF"
            cmd.Parameters.Add(New SqlParameter("@PES_CODIGO", SqlDbType.Int)).Value = PES_CODIGO
            cmd.Parameters.Add(New SqlParameter("@PF_DATA_NASCIMENTO", SqlDbType.Date)).Value = PF_DATA_NASCIMENTO
            cmd.Parameters.Add(New SqlParameter("@PF_RG", SqlDbType.VarChar, 15)).Value = PF_RG
            cmd.Parameters.Add(New SqlParameter("@PF_CPF", SqlDbType.Char, 14)).Value = PF_CPF

            cmd.CommandText = sql
            objD.executacomando(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing

    End Sub


    ''ALTERAR PESSOA JURIDICA
    Private Sub alterar(ByVal PES_CODIGO As Integer,
                     ByVal PJ_NOME_FANTASIA As String, ByVal PJ_RAMO As String, ByVal PJ_CNPJ As String, ByVal PJ_IE As String)
        cmd = New SqlCommand
        objD = New Dados
        Try
            sql = "UPDATE PESSOA_JURIDICA SET PJ_NOME_FANTASIA=@PJ_NOME_FANTASIA, PJ_RAMO=@PJ_RAMO, PJ_CNPJ=@PJ_CNPJ, PJ_IE=@PJ_IE"

            cmd.Parameters.Add(New SqlParameter("@PES_CODIGO", SqlDbType.Int)).Value = PES_CODIGO
            cmd.Parameters.Add(New SqlParameter("@PJ_NOME_FANTASIA", SqlDbType.VarChar, 150)).Value = PJ_NOME_FANTASIA
            cmd.Parameters.Add(New SqlParameter("@PJ_RAMO", SqlDbType.VarChar, 50)).Value = PJ_RAMO
            cmd.Parameters.Add(New SqlParameter("@PJ_CNPJ", SqlDbType.VarChar, 19)).Value = PJ_CNPJ
            cmd.Parameters.Add(New SqlParameter("@PJ_IE", SqlDbType.VarChar, 25)).Value = PJ_IE

            cmd.CommandText = sql
            objD.executacomando(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing

    End Sub

    Sub excluir(ByVal PES_CODIGO As Integer, ByVal PES_FJ As Char)
        cmd = New SqlCommand
        objD = New Dados
        Try
            If (PES_FJ = "F") Then
                sql = "UPDATE PESSOA_FISICA SET PF_ATIVO_SN='N' "
            ElseIf (PES_FJ = "J") Then
                sql = "UPDATE PESSOA_JURIDICA SET PJ_ATIVO_SN='N' "
            End If
            sql += "WHERE PES_CODIGO=@PES_CODIGO"

            cmd.Parameters.Add(New SqlParameter("@PES_CODIGO", SqlDbType.Int)).Value = PES_CODIGO

            cmd.CommandText = sql
            objD.executacomando(cmd)

            ''DELETAR DA TABELA PESSOA
            'sql = "DELETE FROM PESSOA WHERE PES_CODIGO=@PES_CODIGO"
            'cmd.CommandText = sql
            'objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        sql = Nothing
    End Sub

    Public Function localizar(ByVal PES_FJ As Char, ByVal CODIGO As Integer, ByVal NOME As String) As DataTable
        Dim tab As DataTable = Nothing

        Try
            cmd = New SqlCommand
            objD = New Dados

            If (PES_FJ = "F") Then
                sql = "SELECT * FROM PESSOA P, PESSOA_FISICA PF"
                If (NOME <> "") Or (NOME <> Nothing) Then
                    sql = "SELECT * FROM PESSOA P, PESSOA_FISICA PF WHERE P.PES_NOME LIKE @nome and pf_ativo_sn='S'"
                    cmd.Parameters.Add(New SqlParameter("@NOME", SqlDbType.VarChar, 150)).Value = NOME
                    If (CODIGO <> Nothing) Then
                        sql = "SELECT * FROM PESSOA P, PESSOA_FISICA PF WHERE P.PES_CODIGO=PF.PES_CODIGO AND PF.PES_CODIGO=@CODIGO AND P.PES_NOME like @NOME and pf_ativo_sn='S' "
                        cmd.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Int)).Value = CODIGO
                    Else
                        sql = "SELECT * FROM PESSOA P, PESSOA_FISICA PF WHERE P.PES_CODIGO=PF.PES_CODIGO AND P.PES_NOME=@NOME and pf_ativo_sn='S'"
                    End If
                ElseIf (CODIGO <> Nothing) Then
                    sql = "SELECT * FROM PESSOA P, PESSOA_FISICA PF WHERE P.PES_CODIGO=PF.PES_CODIGO AND PF.PF_CODIGO=@CODIGO and pf_ativo_sn='S'"
                    cmd.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Int)).Value = CODIGO
                Else
                    sql = "SELECT * FROM PESSOA P, PESSOA_FISICA PF WHERE P.PES_CODIGO=PF.PES_CODIGO and pf_ativo_sn='S'"
                End If

            ElseIf (PES_FJ = "J") Then
                sql = "SELECT * FROM PESSOA P, PESSOA_JURIDICA PJ "
                If (NOME <> "") Or (NOME <> Nothing) Then
                    cmd.Parameters.Add(New SqlParameter("@NOME", SqlDbType.VarChar, 150)).Value = "%" + NOME + "%"
                    If (CODIGO <> Nothing) Then
                        sql += "WHERE P.PES_CODIGO=PJ.PES_CODIGO AND PJ.PES_CODIGO=@CODIGO AND P.PES_NOME like @NOME and pj_ativo_sn='S'"
                        cmd.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Int)).Value = CODIGO
                    Else
                        sql += "WHERE P.PES_CODIGO=PJ.PES_CODIGO AND P.PES_NOME=@NOME and pj_ativo_sn='S'"
                    End If
                ElseIf (CODIGO <> Nothing) Then
                    sql += "WHERE P.PES_CODIGO=PJ.PES_CODIGO AND PJ.PJ_CODIGO=@CODIGO and pj_ativo_sn='S'"
                    cmd.Parameters.Add(New SqlParameter("@CODIGO", SqlDbType.Int)).Value = CODIGO
                Else
                    sql += "WHERE P.PES_CODIGO=PJ.PES_CODIGO and pj_ativo_sn='S'"
                End If
            End If

            cmd.CommandText = sql
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizarProxAnterior(ByVal PES_FJ As Char, ByVal descricao As String, ByVal CODIGO As Integer) As DataTable

        cmd = New SqlCommand
        objD = New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                If (PES_FJ = "F") Then
                    sql = "SELECT PF_CODIGO FROM PESSOA_FISICA WHERE PF_CODIGO = (SELECT MIN(PF_CODIGO) FROM PESSOA_FISICA WHERE PF_CODIGO > @CODIGO) and pf_ativo_sn='S'"
                ElseIf (PES_FJ = "J") Then
                    sql = "SELECT PJ_CODIGO FROM PESSOA_JURIDICA WHERE PJ_CODIGO = (SELECT MIN(PJ_CODIGO) FROM PESSOA_JURIDICA WHERE PJ_CODIGO > @CODIGO) and pj_ativo_sn='S'"
                End If

            ElseIf (descricao = "anterior") Then
                If (PES_FJ = "F") Then
                    sql = "SELECT PF_CODIGO FROM PESSOA_FISICA WHERE PF_CODIGO = (SELECT MAX(PF_CODIGO) FROM PESSOA_FISICA WHERE PF_CODIGO < @CODIGO) and pf_ativo_sn='S'"
                ElseIf (PES_FJ = "J") Then
                    sql = "SELECT PJ_CODIGO FROM PESSOA_JURIDICA WHERE PJ_CODIGO = (SELECT MAX(PJ_CODIGO) FROM PESSOA_JURIDICA WHERE PJ_CODIGO < @CODIGO) and pj_ativo_sn='S'"
                End If
            End If
            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = CODIGO
            'Execução do comando
            cmd.CommandText = sql
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        'tab = Nothing
    End Function

    Function validarCliente(ByVal CAMPO As String, ByVal TIPO As String) As Boolean
        cmd = New SqlCommand
        objD = New Dados
        Dim tab As DataTable
        Try
            If (TIPO = "F") Then
                sql = "SELECT * FROM PESSOA_FISICA WHERE PF_CPF=@CONDICAO"
                cmd.Parameters.Add(New SqlParameter("@CONDICAO", SqlDbType.VarChar)).Value = CAMPO
                cmd.CommandText = sql
            ElseIf (TIPO = "J") Then
                sql = "SELECT * FROM PESSOA_JURIDICA WHERE PJ_CNPJ=@CONDICAO"
                cmd.Parameters.Add(New SqlParameter("@CONDICAO", SqlDbType.VarChar)).Value = CAMPO
                cmd.CommandText = sql
            End If

            tab = objD.executaConsulta(cmd)
            If (tab.Rows.Count <> 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Function

    Function localizarPrimeiroUltimo(ByVal opcao As String, ByVal tipopessoa As Char) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (opcao = "ultimo") Then
                If (tipopessoa = "F") Then
                    sql = "SELECT PF_CODIGO=MAX(PF_CODIGO) FROM PESSOA_FISICA PF INNER JOIN CLIENTES C ON PF.PES_CODIGO = C.PES_CODIGO where pf_ativo_sn='S'"
                ElseIf tipopessoa = "J" Then
                    sql = "SELECT PJ_CODIGO = MAX(PJ_CODIGO) FROM PESSOA_JURIDICA PJ INNER JOIN CLIENTES C ON PJ.PES_CODIGO = C.PES_CODIGO where pj_ativo_sn='S'"
                End If
            ElseIf (opcao = "primeiro") Then
                If (tipopessoa = "F") Then
                    sql = "SELECT PF_CODIGO = MIN(PF_CODIGO) FROM PESSOA_FISICA PF INNER JOIN CLIENTES C ON PF.PES_CODIGO = C.PES_CODIGO where pf_ativo_sn='S'"
                ElseIf tipopessoa = "J" Then
                    sql = "SELECT PJ_CODIGO = MIN(PJ_CODIGO) FROM PESSOA_JURIDICA PJ INNER JOIN CLIENTES C ON PJ.PES_CODIGO = C.PES_CODIGO where pj_ativo_sn='S'"
                End If
            End If
            cmd.CommandText = sql
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizar(ByVal nome As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (nome = "ultimo") Then
                cmd.CommandText = "SELECT pes_codigo = max(pes_codigo) FROM pessoa"
            ElseIf (nome = "primeiro") Then
                cmd.CommandText = "SELECT pes_codigo = min(pes_codigo) FROM pessoa"

            Else
                'Comando para localizar vários registros no banco
                cmd.CommandText = "SELECT * FROM pessoa p, Clientes c WHERE c.pes_codigo = p.pes_codigo and pes_nome like @descricao"

                'Parâmetro de entrada
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = nome + "%"
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            cmd.CommandText = "select * from pessoa  where pes_codigo = @codigo"

            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo

            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function


    Function localizarCliente(ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            cmd.CommandText = "select * from pessoa p, pessoa_fisica pf where p.pes_codigo=pf.pes_codigo and pf_codigo=@codigo"

            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo

            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    'Function localizarportipo(ByVal tipo As Char) As DataTable
    '    Dim cmd As New SqlCommand
    '    Dim objD As New Dados
    '    Dim tab As DataTable = Nothing
    '    Try
    '        If tipo = "F" Then
    '            cmd.CommandText = "select * from pessoa_fisica pf where pf.pes like @tipo"
    '        End If


    '        cmd.Parameters.Add(New SqlParameter("@tipo", SqlDbType.Char)).Value = tipo & "%"

    '        tab = objD.executaConsulta(cmd)

    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    cmd = Nothing
    '    objD = Nothing
    '    Return tab
    '    tab = Nothing
    'End Function
End Class
